const { query } = require('../config/db');

class Product {
  /**
   * 根据ID查找商品
   * @param {number} id - 商品ID
   * @returns {Promise<Object|null>} 商品信息
   */
  static async findById(id) {
    try {
      const products = await query(`
        SELECT p.*, c.name as category_name 
        FROM products p 
        LEFT JOIN categories c ON p.category_id = c.id 
        WHERE p.id = ?
      `, [id]);
      
      if (products.length === 0) return null;

      const product = products[0];

      // 确保数值字段为正确的数据类型
      if (product.price !== null && product.price !== undefined) {
        product.price = parseFloat(product.price);
      }
      if (product.market_price !== null && product.market_price !== undefined) {
        product.market_price = parseFloat(product.market_price);
      }
      if (product.stock_quantity !== null && product.stock_quantity !== undefined) {
        product.stock_quantity = parseInt(product.stock_quantity);
      }

      // 解析JSON字段
      if (product.gallery_image_urls) {
        try {
          product.gallery_image_urls = JSON.parse(product.gallery_image_urls);
        } catch (e) {
          product.gallery_image_urls = [];
        }
      } else {
        product.gallery_image_urls = [];
      }

      if (product.detail_image_urls) {
        try {
          product.detail_image_urls = JSON.parse(product.detail_image_urls);
        } catch (e) {
          product.detail_image_urls = [];
        }
      } else {
        product.detail_image_urls = [];
      }
      
      return product;
    } catch (error) {
      console.error('查找商品失败:', error);
      throw error;
    }
  }

  /**
   * 根据SKU查找商品
   * @param {string} sku - 商品SKU
   * @returns {Promise<Object|null>} 商品信息
   */
  static async findBySku(sku) {
    try {
      const products = await query(
        'SELECT * FROM products WHERE sku = ?',
        [sku]
      );
      return products.length > 0 ? products[0] : null;
    } catch (error) {
      console.error('查找商品失败:', error);
      throw error;
    }
  }

  /**
   * 创建商品
   * @param {Object} productData - 商品数据
   * @returns {Promise<Object>} 创建的商品信息
   */
  static async create(productData) {
    try {
      const {
        name, sku, category_id, description, price, market_price,
        stock_quantity, main_image_url, gallery_image_urls, detail_image_urls, status
      } = productData;

      const result = await query(`
        INSERT INTO products (
          name, sku, category_id, description, price, market_price,
          stock_quantity, main_image_url, gallery_image_urls, detail_image_urls, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [
        name, sku, category_id, description, price, market_price,
        stock_quantity, main_image_url, 
        gallery_image_urls ? JSON.stringify(gallery_image_urls) : null,
        detail_image_urls ? JSON.stringify(detail_image_urls) : null,
        status || 'draft'
      ]);

      return await this.getById(result.insertId);
    } catch (error) {
      console.error('创建商品失败:', error);
      throw error;
    }
  }

  /**
   * 更新商品
   * @param {number} id - 商品ID
   * @param {Object} updateData - 更新数据
   * @returns {Promise<Object>} 更新后的商品信息
   */
  static async update(id, updateData) {
    try {
      const fields = [];
      const values = [];

      Object.keys(updateData).forEach(key => {
        if (updateData[key] !== undefined) {
          if (key === 'gallery_image_urls' || key === 'detail_image_urls') {
            fields.push(`${key} = ?`);
            values.push(JSON.stringify(updateData[key]));
          } else {
            fields.push(`${key} = ?`);
            values.push(updateData[key]);
          }
        }
      });

      if (fields.length === 0) {
        throw new Error('没有要更新的字段');
      }

      values.push(id);
      await query(
        `UPDATE products SET ${fields.join(', ')} WHERE id = ?`,
        values
      );

      return await this.getById(id);
    } catch (error) {
      console.error('更新商品失败:', error);
      throw error;
    }
  }

  /**
   * 删除商品
   * @param {number} id - 商品ID
   * @returns {Promise<boolean>} 删除结果
   */
  static async delete(id) {
    try {
      const result = await query('DELETE FROM products WHERE id = ?', [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('删除商品失败:', error);
      throw error;
    }
  }

  /**
   * 批量删除商品
   * @param {Array} ids - 商品ID数组
   * @returns {Promise<boolean>} 删除结果
   */
  static async batchDelete(ids) {
    try {
      if (!ids || ids.length === 0) {
        return false;
      }

      const placeholders = ids.map(() => '?').join(',');
      const result = await query(
        `DELETE FROM products WHERE id IN (${placeholders})`,
        ids
      );
      return result.affectedRows > 0;
    } catch (error) {
      console.error('批量删除商品失败:', error);
      throw error;
    }
  }

  /**
   * 获取商品列表（分页）
   * @param {Object} options - 查询选项
   * @returns {Promise<Object>} 商品列表和总数
   */
  static async getList(options = {}) {
    try {
      const {
        page = 1,
        pageSize = 10,
        search = '',
        category_id = null,
        parent_category_id = null,
        status = null,
        sort = 'created_at',
        order = 'DESC'
      } = options;

      const safePage = Math.max(1, Number(page) || 1);
      const safePageSize = Math.max(1, Number(pageSize) || 10);
      const offset = (safePage - 1) * safePageSize;

      let whereClause = 'WHERE 1=1';
      const params = [];

      if (search && String(search).trim()) {
        const kw = `%${String(search).trim()}%`;
        whereClause += ' AND (p.name LIKE ? OR p.sku LIKE ?)';
        params.push(kw, kw);
      }

      if (category_id !== null && category_id !== undefined && String(category_id).trim() !== '') {
        whereClause += ' AND p.category_id = ?';
        params.push(Number(category_id));
      }

      // 如果指定了父分类ID，查询该分类及其所有子分类的商品
      if (parent_category_id !== null && parent_category_id !== undefined && String(parent_category_id).trim() !== '') {
        whereClause += ' AND (p.category_id = ? OR p.category_id IN (SELECT id FROM categories WHERE parent_id = ?))';
        params.push(Number(parent_category_id), Number(parent_category_id));
      }

      if (status && String(status).trim()) {
        whereClause += ' AND p.status = ?';
        params.push(String(status).trim());
      }

      // 统计总数
      const countSql = `
        SELECT COUNT(*) AS total
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        ${whereClause}
      `;
      const countRows = await query(countSql, params);
      const total = countRows[0] ? Number(countRows[0].total) : 0;

      // 排序白名单
      const sortWhitelist = new Set(['created_at', 'price', 'sales_count', 'stock_quantity', 'id']);
      const sortField = sortWhitelist.has(String(sort)) ? String(sort) : 'created_at';
      const orderDir = String(order).toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

      // 列表（直接拼接数字，避免 LIMIT 占位符兼容性问题）
      const listSql = `
        SELECT
          p.*,
          c.name as category_name
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        ${whereClause}
        ORDER BY p.${sortField} ${orderDir}
        LIMIT ${Number(offset)}, ${Number(safePageSize)}
      `;
      const products = await query(listSql, params);

      // 处理数据类型
      products.forEach(product => {
        if (product.price !== null && product.price !== undefined) {
          product.price = parseFloat(product.price);
        }
        if (product.market_price !== null && product.market_price !== undefined) {
          product.market_price = parseFloat(product.market_price);
        }
        if (product.stock_quantity !== null && product.stock_quantity !== undefined) {
          product.stock_quantity = parseInt(product.stock_quantity);
        }
      });

      return { products, total };
    } catch (error) {
      console.error('获取商品列表失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID获取单个商品
   * @param {number} id - 商品ID
   * @returns {Promise<Object>} 商品信息
   */
  static async getById(id) {
    try {
      const result = await query(`
        SELECT
          p.*,
          c.name as category_name
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE p.id = ?
      `, [id]);

      if (result.length === 0) {
        throw new Error('商品不存在');
      }

      const product = result[0];

      // 确保数值字段为正确的数据类型
      if (product.price !== null && product.price !== undefined) {
        product.price = parseFloat(product.price);
      }
      if (product.market_price !== null && product.market_price !== undefined) {
        product.market_price = parseFloat(product.market_price);
      }
      if (product.stock_quantity !== null && product.stock_quantity !== undefined) {
        product.stock_quantity = parseInt(product.stock_quantity);
      }

      // 处理图片数组
      if (product.gallery_image_urls) {
        try {
          product.gallery_image_urls = JSON.parse(product.gallery_image_urls);
        } catch (e) {
          product.gallery_image_urls = [];
        }
      } else {
        product.gallery_image_urls = [];
      }

      return product;
    } catch (error) {
      console.error('获取商品失败:', error);
      throw error;
    }
  }

  /**
   * 更新库存
   * @param {number} id - 商品ID
   * @param {number} quantity - 库存变化量（正数增加，负数减少）
   * @returns {Promise<boolean>} 更新结果
   */
  static async updateStock(id, quantity) {
    try {
      const result = await query(
        'UPDATE products SET stock_quantity = stock_quantity + ? WHERE id = ? AND stock_quantity + ? >= 0',
        [quantity, id, quantity]
      );
      return result.affectedRows > 0;
    } catch (error) {
      console.error('更新库存失败:', error);
      throw error;
    }
  }

  /**
   * 获取库存预警商品
   * @param {number} threshold - 库存阈值
   * @returns {Promise<Array>} 库存不足的商品列表
   */
  static async getLowStockProducts(threshold = 5) {
    try {
      const products = await query(`
        SELECT 
          p.id, p.name, p.sku, p.stock_quantity, p.main_image_url,
          c.name as category_name
        FROM products p 
        LEFT JOIN categories c ON p.category_id = c.id 
        WHERE p.stock_quantity <= ? AND p.status = 'active'
        ORDER BY p.stock_quantity ASC
      `, [threshold]);

      return products;
    } catch (error) {
      console.error('获取库存预警商品失败:', error);
      throw error;
    }
  }

  /**
   * 获取商品统计数据
   * @returns {Promise<Object>} 统计数据
   */
  static async getStats() {
    try {
      // 商品总数
      const totalResult = await query('SELECT COUNT(*) as total FROM products');
      const total = totalResult[0].total;

      // 上架商品数
      const activeResult = await query('SELECT COUNT(*) as active FROM products WHERE status = "active"');
      const active = activeResult[0].active;

      // 库存预警商品数
      const lowStockResult = await query('SELECT COUNT(*) as lowStock FROM products WHERE stock_quantity <= 5 AND status = "active"');
      const lowStock = lowStockResult[0].lowStock;

      return {
        total,
        active,
        lowStock
      };
    } catch (error) {
      console.error('获取商品统计失败:', error);
      throw error;
    }
  }
}

module.exports = Product;
